package com.lrkj.back.controller;

import java.io.ByteArrayOutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSON;
import com.github.ExcelUtils;
import com.github.annotation.ExcelField;
import com.lrkj.back.StartUpOnLoadData;
import com.lrkj.back.Const;
import com.lrkj.back.entity.*;
import com.lrkj.back.service.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import com.lrkj.back.vo.PinmingNumVO;
import cn.com.cbons.mamisay.parent.exception.BusinessException;

import com.github.pagehelper.PageInfo;

@Controller
@RequestMapping("/tongji")
public class TongjiController extends BaseController {
    @Autowired
    private ZsPackageImformationService packageImformationService;
    @Autowired
    private ZsEquipService equipService;
    @Autowired
    private TbDepartmentService departService;
    @Autowired
    private TbEmployeeService employeeService;
    @Autowired
    private TbPackageMubanService packageMubanService;
    @Autowired
    private ZsWupinYcpFafangService wupinYcpFafangSerivice;
    @Autowired
    private ZsWupinFafangService wupinfafangService;
    @Autowired
    private ZsYicipinRukumingxiService yicipinRukumingxiService;
    @Autowired
    private ZsYicipinRukuService yicipinRukuService;
    @Autowired
    private ZsYicipinKucunService ycpKuncunService;
    @Autowired
    private ZsWupinBaoFafangService wupinbaoFafangService;
    @Autowired
    private ZsShenqingService shenqingService;
    @Autowired
    private ZsShenqingShoushubaoMingxiService ssbshenqingService;
    @Autowired
    private ZsKeshiKucunService kucunService;
    @Autowired
    private ZsHuishouMingxiService zsHuishouMingxiService;
    @Autowired
    private TbDepartmentService tbDepartmentService;

    @RequestMapping("/exportPackage")
    public void exportPackage(@RequestParam String data, HttpServletResponse response) throws Exception {
//		List<ZsHuishou> list = JSON.parseArray(data);
        response.setContentType("application/octet-stream");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String name = "回收报表" + sdf.format(new Date()) + "-" + System.currentTimeMillis() + ".xlsx";
        response.setHeader(
                "content-disposition", "attachment;filename=" + new String(name.getBytes("UTF-8"), "iso-8859-1")
        );
        ByteArrayOutputStream os = new ByteArrayOutputStream();
//		ExcelUtils.getInstance().exportObjects2Excel(list, Rec.class, true, null, true, os);
        byte[] bytes = os.toByteArray();
        ServletOutputStream outputStream = response.getOutputStream();
        outputStream.write(bytes);
        outputStream.close();
    }

    //统计--回收统计  （点击查询）
    @RequestMapping("/huishoutongji.do")
    public ModelAndView huishoutongji(String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Integer pageNo = request.getParameter("pageIndex") == null ? 1 : Integer.valueOf(request.getParameter("pageIndex"));
        Map<String, Object> map = new HashMap<String, Object>();
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date startTime = null;
        Date endTime = null;
        Map<String, Object> tiaojianMap = new HashMap<>();
        List<String> packageIds = new ArrayList<>();
        List<String> tiaomaList = new ArrayList<>();
        List<String> intList = new ArrayList<>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<TbDepartment> department = departService.findDepartment(tiaojianMap);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            List<TbPackageMuban> packageMuban = packageMubanService.findPackage(tiaojianMap);
            if (!department.isEmpty()) {
                Map<String, Object> mubanMap = new HashMap<String, Object>();
                for (TbDepartment tbDepartment : department) {
                    if (!intList.contains(tbDepartment.getDepartmentId())) {
                        intList.add(tbDepartment.getDepartmentId());
                    }
                }
                mubanMap.put("packageDepartidArr", intList);
                List<TbPackageMuban> packageMubans = packageMubanService.findAllPackageNoPageList(mubanMap);
                if (!packageMubans.isEmpty()) {
                    for (TbPackageMuban tbPackageMuban : packageMubans) {
                        if (!packageIds.contains(tbPackageMuban.getPackageId())) {
                            packageIds.add(tbPackageMuban.getPackageId());
                        }
                    }
                    map.put("packageIdArr", packageIds);
                } else {
                    packageIds.add("0");
                    map.put("packageIdArr", packageIds);
                }
            }
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                map.put("ren", "huishou");
                map.put("renArr", tiaomaList);
            }
            if (!packageMuban.isEmpty()) {
                for (TbPackageMuban tbPackageMuban : packageMuban) {
                    if (!intList.contains(tbPackageMuban.getPackageId())) {
                        intList.add(tbPackageMuban.getPackageId());
                    }
                }
                map.put("packageIdArr", intList);
            }
            if (department.isEmpty() && employee.isEmpty() && packageMuban.isEmpty()) {
                packageIds.add("0");
                map.put("packageIdArr", packageIds);
            }
        }
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        map.put("chaxun", "huishou");
        map.put("pageNo", pageNo);
        map.put("pageSize", Const.PAGE_SIZE);
        PageInfo<ZsPackageImformation> page = packageImformationService.tongjiChaxunPage(map);
        if (!page.getList().isEmpty()) {
            request.setAttribute("packageinformationList", page.getList());
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", page.getPages());
            request.setAttribute("emMap", StartUpOnLoadData.emMap);
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("totalNum", page.getTotal());
        } else {
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("packageinformationList", "");
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", 1);
            request.setAttribute("totalNum", 0);
        }
        return new ModelAndView("tongji/huishou_tongji");
    }

    //统计--回收统计   点击导出
    @RequestMapping("/huishoudaochu.do")
    public void huishoudaochu(String shijian, String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Map<String, Object> map = new HashMap<String, Object>();
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date startTime = null;
        Date endTime = null;
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        List<String> packageIds = new ArrayList<String>();
        List<String> tiaomaList = new ArrayList<String>();
        List<String> intList = new ArrayList<String>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<TbDepartment> department = departService.findDepartment(tiaojianMap);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            List<TbPackageMuban> packageMuban = packageMubanService.findPackage(tiaojianMap);
            if (!department.isEmpty()) {
                Map<String, Object> mubanMap = new HashMap<String, Object>();
                for (TbDepartment tbDepartment : department) {
                    if (!intList.contains(tbDepartment.getDepartmentId())) {
                        intList.add(tbDepartment.getDepartmentId());
                    }
                }
                mubanMap.put("packageDepartidArr", intList);
                List<TbPackageMuban> packageMubans = packageMubanService.findAllPackageNoPageList(mubanMap);
                if (!packageMubans.isEmpty()) {
                    for (TbPackageMuban tbPackageMuban : packageMubans) {
                        if (!packageIds.contains(tbPackageMuban.getPackageId())) {
                            packageIds.add(tbPackageMuban.getPackageId());
                        }
                    }
                    map.put("packageIdArr", packageIds);
                } else {
                    packageIds.add("0");
                    map.put("packageIdArr", packageIds);
                }
            }
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                map.put("ren", "huishou");
                map.put("renArr", tiaomaList);
            }
            if (!packageMuban.isEmpty()) {
                for (TbPackageMuban tbPackageMuban : packageMuban) {
                    if (!intList.contains(tbPackageMuban.getPackageId())) {
                        intList.add(tbPackageMuban.getPackageId());
                    }
                }
                map.put("packageIdArr", intList);
            }
            if (department.isEmpty() && employee.isEmpty() && packageMuban.isEmpty()) {
                packageIds.add("0");
                map.put("packageIdArr", packageIds);
            }
        }
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        map.put("chaxun", "huishou");
        List<ZsPackageImformation> packageImformations = packageImformationService.tongjiChaxunTotal(map);
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("回收统计报表");
        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("包条码");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("包名");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("所属科室");
        cell.setCellStyle(style);
        cell = row.createCell((short) 3);
        cell.setCellValue("回收人");
        cell.setCellStyle(style);
        cell = row.createCell((short) 4);
        cell.setCellValue("回收交接人");
        cell.setCellStyle(style);
        cell = row.createCell((short) 5);
        cell.setCellValue("回收时间");
        cell.setCellStyle(style);
        ZsPackageImformation packageImformation = null;
        for (int i = 0; i < packageImformations.size(); i++) {
            row = sheet.createRow((int) i + 1);
            packageImformation = packageImformations.get(i);
            row.createCell((short) 0).setCellValue(packageImformation.getPackageTiaoma());
            row.createCell((short) 1).setCellValue(packageImformation.getTbPackageMuban().getPackageName());
            if (packageImformation.getTbPackageMuban().getTbDepartment() != null) {
                row.createCell((short) 2).setCellValue(packageImformation.getTbPackageMuban().getTbDepartment().getDepartmentName());
            }
            if (packageImformation.getTbPackageMuban().getTbDepartment() == null) {
                row.createCell((short) 2).setCellValue("厂家");
            }
            row.createCell((short) 3).setCellValue(StartUpOnLoadData.emMap.get(packageImformation.getHuishouHuishourenTiaoma()));
            row.createCell((short) 4).setCellValue(StartUpOnLoadData.emMap.get(packageImformation.getHuishouJiaojierenTiaoma()));
            row.createCell((short) 5).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(packageImformation.getHuishouTime()));
        }
        row = sheet.createRow(packageImformations.size() + 1);
        row.createCell((short) 0).setCellValue("包总数:" + packageImformations.size());
        try {
            String fileName = "回收统计报表.xls";
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);// 指定下载的文件名
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //统计--清洗统计   点击导出
    @RequestMapping("/qingxidaochu.do")
    public void qingxidaochu(String shijian, String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Map<String, Object> map = new HashMap<String, Object>();
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date startTime = null;
        Date endTime = null;
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        List<String> packageIds = new ArrayList<String>();
        if (StringUtils.isNotBlank(shijian)) {
            Integer shijianleixing = Integer.valueOf(shijian);
            if (shijianleixing == 1) {
                map.put("shijian", "kaishi");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "清洗开始时间");
            }
            if (shijianleixing == 2) {
                map.put("shijian", "jieshu");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "清洗结束时间");
            }
            if (shijianleixing == 3) {
                map.put("shijian", "shenhe");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "清洗审核时间");
            }
            if (shijianleixing == 0) {
                map.put("shijian", "shijian");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "选择时间类型");
            }
        }
        List<String> tiaomaList = new ArrayList<String>();
        List<String> intList = new ArrayList<String>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<ZsEquip> equips = equipService.findEquipListByType(tiaojianMap);
            List<TbDepartment> department = departService.findDepartment(tiaojianMap);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            List<TbPackageMuban> packageMuban = packageMubanService.findPackage(tiaojianMap);
            //判断有无此设备
            if (!equips.isEmpty()) {
                for (ZsEquip zsEquip : equips) {
                    if (!tiaomaList.contains(zsEquip.getEquipId())) {
                        tiaomaList.add(zsEquip.getEquipId().toString());
                    }
                }
                map.put("qingxiShebeiTiaomaArr", tiaomaList);
            }
            //判断有无此科室
            if (!department.isEmpty()) {
                Map<String, Object> mubanMap = new HashMap<String, Object>();
                for (TbDepartment tbDepartment : department) {
                    if (!intList.contains(tbDepartment.getDepartmentId())) {
                        intList.add(tbDepartment.getDepartmentId());
                    }
                }
                mubanMap.put("packageDepartidArr", intList);
                List<TbPackageMuban> packageMubans = packageMubanService.findAllPackageNoPageList(mubanMap);
                if (!packageMubans.isEmpty()) {
                    for (TbPackageMuban tbPackageMuban : packageMubans) {
                        if (!packageIds.contains(tbPackageMuban.getPackageId())) {
                            packageIds.add(tbPackageMuban.getPackageId());
                        }
                    }
                    map.put("packageIdArr", packageIds);
                } else {
                    packageIds.add("0");
                    map.put("packageIdArr", packageIds);
                }
            }
            //判断有无此员工
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                map.put("ren", "qingxi");
                map.put("renArr", tiaomaList);
            }
            //判断有无此包模板
            if (!packageMuban.isEmpty()) {
                for (TbPackageMuban tbPackageMuban : packageMuban) {
                    if (!intList.contains(tbPackageMuban.getPackageId())) {
                        intList.add(tbPackageMuban.getPackageId());
                    }
                }
                map.put("packageIdArr", intList);
            }
            if (equips.isEmpty() && department.isEmpty() && employee.isEmpty() && packageMuban.isEmpty()) {
                packageIds.add("0");
                map.put("packageIdArr", packageIds);
//				map.put("qingxiQingxirenTiaoma", "0");
            }
        }
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        map.put("chaxun", "qingxi");
        map.put("qingxiLiushui", "qingxiLiushui");
        List<ZsPackageImformation> packageImformations = packageImformationService.tongjiChaxunTotal(map);
        //导出成excel表格
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("清洗统计报表");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("包条码");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("包名");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("所属科室");
        cell.setCellStyle(style);
        cell = row.createCell((short) 3);
        cell.setCellValue("清洗设备");
        cell.setCellStyle(style);
        cell = row.createCell((short) 4);
        cell.setCellValue("清洗人");
        cell.setCellStyle(style);
        cell = row.createCell((short) 5);
        cell.setCellValue("清洗开始时间");
        cell.setCellStyle(style);
        cell = row.createCell((short) 6);
        cell.setCellValue("清洗结束时间");
        cell.setCellStyle(style);
        cell = row.createCell((short) 7);
        cell.setCellValue("清洗审核人");
        cell.setCellStyle(style);
        cell = row.createCell((short) 8);
        cell.setCellValue("清洗审核时间");
        cell.setCellStyle(style);
        cell = row.createCell((short) 9);
        cell.setCellValue("清洗合格");
        cell.setCellStyle(style);
        ZsPackageImformation packageImformation = null;
        for (int i = 0; i < packageImformations.size(); i++) {
            row = sheet.createRow((int) i + 1);
            packageImformation = packageImformations.get(i);
            // 第四步，创建单元格，并设置值
            row.createCell((short) 0).setCellValue(packageImformation.getPackageTiaoma());
            row.createCell((short) 1).setCellValue(packageImformation.getTbPackageMuban().getPackageName());
            if (packageImformation.getTbPackageMuban().getTbDepartment() != null) {
                row.createCell((short) 2).setCellValue(packageImformation.getTbPackageMuban().getTbDepartment().getDepartmentName());
            }
            if (packageImformation.getTbPackageMuban().getTbDepartment() == null) {
                row.createCell((short) 2).setCellValue("厂家");
            }
            if (packageImformation.getMiejunShebeiTiaoma() != null) {
                row.createCell((short) 3).setCellValue(StartUpOnLoadData.equipMap.get(Long.valueOf(packageImformation.getMiejunShebeiTiaoma())).getEquipName());
            }
            row.createCell((short) 4).setCellValue(StartUpOnLoadData.emMap.get(packageImformation.getQingxiQingxirenTiaoma()));
            if (packageImformation.getQingxiKaishiTime() != null) {
                row.createCell((short) 5).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(packageImformation.getQingxiKaishiTime()));
            }
            if (packageImformation.getQingxiJieshuTime() != null) {
                row.createCell((short) 6).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(packageImformation.getQingxiJieshuTime()));
            }
            row.createCell((short) 7).setCellValue(StartUpOnLoadData.emMap.get(packageImformation.getQingxiShenherenTiaoma()));
            if (packageImformation.getQingxiShenherenTime() != null) {
                row.createCell((short) 8).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(packageImformation.getQingxiShenherenTime()));
            }
            row.createCell((short) 9).setCellValue(packageImformation.getQingxiHege() == 1 ? "合格" : "不合格");
        }
        row = sheet.createRow(packageImformations.size() + 1);
        row.createCell((short) 0).setCellValue("包总数:" + packageImformations.size());
        try {
            String fileName = "清洗统计报表.xls";
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);// 指定下载的文件名
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //统计--清洗统计   （点击查询）
    @RequestMapping("/qingxitongji.do")
    public ModelAndView qingxitongji(String shijian, String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Integer pageNo = request.getParameter("pageIndex") == null ? 1 : Integer.valueOf(request.getParameter("pageIndex"));
        Map<String, Object> map = new HashMap<String, Object>();
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date startTime = null;
        Date endTime = null;
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        List<String> packageIds = new ArrayList<String>();
        if (StringUtils.isNotBlank(shijian)) {
            Integer shijianleixing = Integer.valueOf(shijian);
            if (shijianleixing == 1) {
                map.put("shijian", "kaishi");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "清洗开始时间");
            }
            if (shijianleixing == 2) {
                map.put("shijian", "jieshu");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "清洗结束时间");
            }
            if (shijianleixing == 3) {
                map.put("shijian", "shenhe");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "清洗审核时间");
            }
            if (shijianleixing == 0) {
                map.put("shijian", "shijian");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "选择时间类型");
            }
        }

        List<String> tiaomaList = new ArrayList<String>();
        List<String> intList = new ArrayList<String>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<ZsEquip> equips = equipService.findEquipListByType(tiaojianMap);
            List<TbDepartment> department = departService.findDepartment(tiaojianMap);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            List<TbPackageMuban> packageMuban = packageMubanService.findPackage(tiaojianMap);
            //判断有无此设备
            if (!equips.isEmpty()) {
                for (ZsEquip zsEquip : equips) {
                    if (!tiaomaList.contains(zsEquip.getEquipId())) {
                        tiaomaList.add(zsEquip.getEquipId().toString());
                    }
                }
                map.put("qingxiShebeiTiaomaArr", tiaomaList);
            }
            //判断有无此科室
            if (!department.isEmpty()) {
                Map<String, Object> mubanMap = new HashMap<String, Object>();
                for (TbDepartment tbDepartment : department) {
                    if (!intList.contains(tbDepartment.getDepartmentId())) {
                        intList.add(tbDepartment.getDepartmentId());
                    }
                }
                mubanMap.put("packageDepartidArr", intList);
                List<TbPackageMuban> packageMubans = packageMubanService.findAllPackageNoPageList(mubanMap);
                if (!packageMubans.isEmpty()) {
                    for (TbPackageMuban tbPackageMuban : packageMubans) {
                        if (!packageIds.contains(tbPackageMuban.getPackageId())) {
                            packageIds.add(tbPackageMuban.getPackageId());
                        }
                    }
                    map.put("packageIdArr", packageIds);
                } else {
                    packageIds.add("0");
                    map.put("packageIdArr", packageIds);
                }
            }
            //判断有无此员工
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                map.put("ren", "qingxi");
                map.put("renArr", tiaomaList);
            }
            //判断有无此包模板
            if (!packageMuban.isEmpty()) {
                for (TbPackageMuban tbPackageMuban : packageMuban) {
                    if (!intList.contains(tbPackageMuban.getPackageId())) {
                        intList.add(tbPackageMuban.getPackageId());
                    }
                }
                map.put("packageIdArr", intList);
            }
            if (equips.isEmpty() && department.isEmpty() && employee.isEmpty() && packageMuban.isEmpty()) {
                packageIds.add("0");
                map.put("packageIdArr", packageIds);
//				map.put("qingxiQingxirenTiaoma", "0");
            }
        }
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        map.put("chaxun", "qingxi");
        map.put("pageNo", pageNo);
        map.put("pageSize", Const.PAGE_SIZE);
        map.put("qingxiLiushui", "qingxiLiushui");
        PageInfo<ZsPackageImformation> page = packageImformationService.tongjiChaxunPage(map);
        if (!page.getList().isEmpty()) {
            request.setAttribute("packageinformationList", page.getList());
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", page.getPages());
            request.setAttribute("equipMap", StartUpOnLoadData.equipMap);
            request.setAttribute("dictMap", StartUpOnLoadData.singleDataMap);
            request.setAttribute("emMap", StartUpOnLoadData.emMap);
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("totalNum", page.getTotal());
        } else {
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("packageinformationList", "");
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", 1);
            request.setAttribute("totalNum", 0);
        }
        return new ModelAndView("tongji/qingxi_tongji");
    }

    //统计--灭菌统计   （点击查询）
    @RequestMapping("/miejuntongji.do")
    public ModelAndView miejuntongji(String shijian, String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Integer pageNo = request.getParameter("pageIndex") == null ? 1 : Integer.valueOf(request.getParameter("pageIndex"));
        Map<String, Object> map = new HashMap<String, Object>();
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date startTime = null;
        Date endTime = null;
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        List<String> packageIds = new ArrayList<String>();
        if (StringUtils.isNotBlank(shijian)) {
            Integer shijianleixing = Integer.valueOf(shijian);
            if (shijianleixing == 1) {
                map.put("shijian", "kaishi");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "灭菌开始时间");
            }
            if (shijianleixing == 2) {
                map.put("shijian", "jieshu");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "灭菌结束时间");
            }
            if (shijianleixing == 3) {
                map.put("shijian", "shenhe");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "灭菌审核时间");
            }
            if (shijianleixing == 0) {
                map.put("shijian", "shijian");
                request.setAttribute("shijian", shijian);
                request.setAttribute("lx", "选择时间类型");
            }
        }
        List<String> tiaomaList = new ArrayList<String>();
        List<String> intList = new ArrayList<String>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<ZsEquip> equips = equipService.findEquipListByType(tiaojianMap);
            List<TbDepartment> department = departService.findDepartment(tiaojianMap);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            List<TbPackageMuban> packageMuban = packageMubanService.findPackage(tiaojianMap);
            //判断有无此设备
            if (!equips.isEmpty()) {
                for (ZsEquip zsEquip : equips) {
                    if (!tiaomaList.contains(zsEquip.getEquipId())) {
                        tiaomaList.add(zsEquip.getEquipId().toString());
                    }
                }
                map.put("miejunShebeiTiaomaArr", tiaomaList);
            }
            //判断有无此科室
            if (!department.isEmpty()) {
                Map<String, Object> mubanMap = new HashMap<String, Object>();
                for (TbDepartment tbDepartment : department) {
                    if (!intList.contains(tbDepartment.getDepartmentId())) {
                        intList.add(tbDepartment.getDepartmentId());
                    }
                }
                mubanMap.put("packageDepartidArr", intList);
                List<TbPackageMuban> packageMubans = packageMubanService.findAllPackageNoPageList(mubanMap);
                if (!packageMubans.isEmpty()) {
                    for (TbPackageMuban tbPackageMuban : packageMubans) {
                        if (!packageIds.contains(tbPackageMuban.getPackageId())) {
                            packageIds.add(tbPackageMuban.getPackageId());
                        }
                    }
                    map.put("packageIdArr", packageIds);
                } else {
                    packageIds.add("0");
                    map.put("packageIdArr", packageIds);
                }
            }
            //判断有无此员工
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                map.put("ren", "miejun");
                map.put("renArr", tiaomaList);
            }
            //判断有无此包模板
            if (!packageMuban.isEmpty()) {
                for (TbPackageMuban tbPackageMuban : packageMuban) {
                    if (!intList.contains(tbPackageMuban.getPackageId())) {
                        intList.add(tbPackageMuban.getPackageId());
                    }
                }
                map.put("packageIdArr", intList);
            }
            if (equips.isEmpty() && department.isEmpty() && employee.isEmpty() && packageMuban.isEmpty()) {
                packageIds.add("0");
                map.put("packageIdArr", packageIds);
//				map.put("miejunMeijunrenTiaoma", "0");
            }
        }
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        map.put("chaxun", "miejun");
        map.put("pageNo", pageNo);
        map.put("pageSize", Const.PAGE_SIZE);
        map.put("miejunHege", 3);
        map.put("miejunLiushui", "miejunliushui");
        PageInfo<ZsPackageImformation> page = packageImformationService.tongjiChaxunPage(map);
        if (!page.getList().isEmpty()) {
            request.setAttribute("packageinformationList", page.getList());
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", page.getPages());
            request.setAttribute("equipMap", StartUpOnLoadData.equipMap);
            request.setAttribute("dictMap", StartUpOnLoadData.singleDataMap);
            request.setAttribute("emMap", StartUpOnLoadData.emMap);
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("totalNum", page.getTotal());
        } else {
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("packageinformationList", "");
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", 1);
            request.setAttribute("totalNum", 0);
        }
        return new ModelAndView("tongji/miejun_tongji");
    }

    //统计--清洗统计   点击导出
    @RequestMapping("/miejundaochu.do")
    public void miejundaochu(String shijian, String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Map<String, Object> map = new HashMap<String, Object>();
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date startTime = null;
        Date endTime = null;
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        List<String> packageIds = new ArrayList<String>();
        if (StringUtils.isNotBlank(shijian)) {
            Integer shijianleixing = Integer.valueOf(shijian);
            if (shijianleixing == 1) {
                map.put("shijian", "kaishi");
            }
            if (shijianleixing == 2) {
                map.put("shijian", "jieshu");
            }
            if (shijianleixing == 3) {
                map.put("shijian", "shenhe");
            }
        }
        List<String> intList = new ArrayList<String>();
        List<String> tiaomaList = new ArrayList<String>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<ZsEquip> equips = equipService.findEquipListByType(tiaojianMap);
            List<TbDepartment> department = departService.findDepartment(tiaojianMap);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            List<TbPackageMuban> packageMuban = packageMubanService.findPackage(tiaojianMap);
            if (!equips.isEmpty()) {
                for (ZsEquip zsEquip : equips) {
                    if (!tiaomaList.contains(zsEquip.getEquipId())) {
                        tiaomaList.add(zsEquip.getEquipId().toString());
                    }
                }
                map.put("miejunShebeiTiaomaArr", tiaomaList);
            }
            if (!department.isEmpty()) {
                Map<String, Object> mubanMap = new HashMap<String, Object>();
                for (TbDepartment tbDepartment : department) {
                    if (!intList.contains(tbDepartment.getDepartmentId())) {
                        intList.add(tbDepartment.getDepartmentId());
                    }
                }
                mubanMap.put("packageDepartidArr", intList);
                List<TbPackageMuban> packageMubans = packageMubanService.findAllPackageNoPageList(mubanMap);
                if (!packageMubans.isEmpty()) {
                    for (TbPackageMuban tbPackageMuban : packageMubans) {
                        if (!packageIds.contains(tbPackageMuban.getPackageId())) {
                            packageIds.add(tbPackageMuban.getPackageId());
                        }
                    }
                    map.put("packageIdArr", packageIds);
                } else {
                    packageIds.add("0");
                    map.put("packageIdArr", packageIds);
                }
            }
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                map.put("ren", "miejun");
                map.put("renArr", tiaomaList);
            }
            if (!packageMuban.isEmpty()) {
                for (TbPackageMuban tbPackageMuban : packageMuban) {
                    if (!intList.contains(tbPackageMuban.getPackageId())) {
                        intList.add(tbPackageMuban.getPackageId());
                    }
                }
                map.put("packageIdArr", intList);
            }
            if (equips.isEmpty() && department.isEmpty() && employee.isEmpty() && packageMuban.isEmpty()) {
                packageIds.add("0");
                map.put("packageIdArr", packageIds);
//				map.put("miejunMeijunrenTiaoma", "0");
            }
        }
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        map.put("chaxun", "miejun");
        map.put("miejunHege", 3);
        map.put("miejunLiushui", "miejunliushui");
        List<ZsPackageImformation> packageImformations = packageImformationService.tongjiChaxunTotal(map);
        //导出成excel表格
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("灭菌统计报表");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("包条码");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("包名");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("所属科室");
        cell.setCellStyle(style);
        cell = row.createCell((short) 3);
        cell.setCellValue("灭菌设备");
        cell.setCellStyle(style);
        cell = row.createCell((short) 4);
        cell.setCellValue("灭菌方式");
        cell.setCellStyle(style);
        cell = row.createCell((short) 5);
        cell.setCellValue("灭菌人");
        cell.setCellStyle(style);
        cell = row.createCell((short) 6);
        cell.setCellValue("灭菌开始时间");
        cell.setCellStyle(style);
        cell = row.createCell((short) 7);
        cell.setCellValue("灭菌结束时间");
        cell.setCellStyle(style);
        cell = row.createCell((short) 8);
        cell.setCellValue("灭菌审核人");
        cell.setCellStyle(style);
        cell = row.createCell((short) 9);
        cell.setCellValue("灭菌审核时间");
        cell.setCellStyle(style);
        cell = row.createCell((short) 10);
        cell.setCellValue("灭菌合格");
        cell.setCellStyle(style);
        ZsPackageImformation packageImformation = null;
        for (int i = 0; i < packageImformations.size(); i++) {
            row = sheet.createRow((int) i + 1);
            packageImformation = packageImformations.get(i);
            // 第四步，创建单元格，并设置值
            row.createCell((short) 0).setCellValue(packageImformation.getPackageTiaoma());
            row.createCell((short) 1).setCellValue(packageImformation.getTbPackageMuban().getPackageName());
            if (packageImformation.getTbPackageMuban().getTbDepartment() != null) {
                row.createCell((short) 2).setCellValue(packageImformation.getTbPackageMuban().getTbDepartment().getDepartmentName());
            }
            if (packageImformation.getTbPackageMuban().getTbDepartment() == null) {
                row.createCell((short) 2).setCellValue("厂家");
            }
            row.createCell((short) 3).setCellValue(StartUpOnLoadData.equipMap.get(Long.valueOf(packageImformation.getMiejunShebeiTiaoma())).getEquipName());
            row.createCell((short) 4).setCellValue(StartUpOnLoadData.singleDataMap.get(Long.valueOf(packageImformation.getTbPackageMuban().getPackageMiejunFangshi())).getDictName());
            row.createCell((short) 5).setCellValue(StartUpOnLoadData.emMap.get(packageImformation.getMiejunMeijunrenTiaoma()));
            row.createCell((short) 6).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(packageImformation.getMiejunKaishiTime()));
            row.createCell((short) 7).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(packageImformation.getMiejunJieshuTime()));
            row.createCell((short) 8).setCellValue(StartUpOnLoadData.emMap.get(packageImformation.getMiejunShenherenTiaoma()));
            row.createCell((short) 9).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(packageImformation.getMiejunShenheTime()));
            row.createCell((short) 10).setCellValue(packageImformation.getMiejunHege() == 3 ? "合格" : "不合格");
        }
        row = sheet.createRow(packageImformations.size() + 1);
        row.createCell((short) 0).setCellValue("包总数:" + packageImformations.size());
        try {
            String fileName = "灭菌统计报表.xls";
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);// 指定下载的文件名
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //统计--包发放统计   （点击查询）
    @RequestMapping("/baofafangtongji.do")
    public ModelAndView fafangtongji(String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Integer pageNo = request.getParameter("pageIndex") == null ? 1 : Integer.valueOf(request.getParameter("pageIndex"));
        Map<String, Object> map = new HashMap<String, Object>();
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date startTime = null;
        Date endTime = null;
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        List<String> packageIds = new ArrayList<String>();
        List<String> intList = new ArrayList<String>();
        List<String> tiaomaList = new ArrayList<String>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<TbDepartment> department = departService.findDepartment(tiaojianMap);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            List<TbPackageMuban> packageMuban = packageMubanService.findPackage(tiaojianMap);
            if (!department.isEmpty()) {
                Map<String, Object> mubanMap = new HashMap<String, Object>();
                for (TbDepartment tbDepartment : department) {
                    if (!intList.contains(tbDepartment.getDepartmentId())) {
                        intList.add(tbDepartment.getDepartmentId());
                    }
                }
                mubanMap.put("packageDepartidArr", intList);
                List<TbPackageMuban> packageMubans = packageMubanService.findAllPackageNoPageList(mubanMap);
                if (!packageMubans.isEmpty()) {
                    for (TbPackageMuban tbPackageMuban : packageMubans) {
                        if (!packageIds.contains(tbPackageMuban.getPackageId())) {
                            packageIds.add(tbPackageMuban.getPackageId());
                        }
                    }
                    map.put("packageIdArr", packageIds);
                } else {
                    packageIds.add("0");
                    map.put("packageIdArr", packageIds);
                }
            }
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                map.put("ren", "fafang");
                map.put("renArr", tiaomaList);
            }
            if (!packageMuban.isEmpty()) {
                for (TbPackageMuban tbPackageMuban : packageMuban) {
                    if (!intList.contains(tbPackageMuban.getPackageId())) {
                        intList.add(tbPackageMuban.getPackageId());
                    }
                }
                map.put("packageIdArr", intList);
            }
            if (department.isEmpty() && employee.isEmpty() && packageMuban.isEmpty()) {
                packageIds.add("0");
                map.put("packageIdArr", packageIds);
            }
        }
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        map.put("chaxun", "fafang");
        map.put("pageNo", pageNo);
        map.put("pageSize", Const.PAGE_SIZE);
        map.put("fafangtime", "fafangtime");
        map.put("fafangLiushui", "fafangLiushui");
        PageInfo<ZsPackageImformation> page = packageImformationService.tongjiChaxunPage(map);
        if (!page.getList().isEmpty()) {
            request.setAttribute("packageinformationList", page.getList());
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", page.getPages());
            request.setAttribute("dictMap", StartUpOnLoadData.singleDataMap);
            request.setAttribute("emMap", StartUpOnLoadData.emMap);
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("totalNum", page.getTotal());
        } else {
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("packageinformationList", "");
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", 1);
            request.setAttribute("totalNum", 0);
        }
        return new ModelAndView("tongji/baofafang_tongji");
    }

    //统计--包发放统计   点击导出
    @RequestMapping("/baofafangdaochu.do")
    public void baofafangdaochu(String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Map<String, Object> map = new HashMap<String, Object>();
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date startTime = null;
        Date endTime = null;
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        List<String> packageIds = new ArrayList<String>();
        List<String> intList = new ArrayList<String>();
        List<String> tiaomaList = new ArrayList<String>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<TbDepartment> department = departService.findDepartment(tiaojianMap);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            List<TbPackageMuban> packageMuban = packageMubanService.findPackage(tiaojianMap);
            if (!department.isEmpty()) {
                Map<String, Object> mubanMap = new HashMap<String, Object>();
                for (TbDepartment tbDepartment : department) {
                    if (!intList.contains(tbDepartment.getDepartmentId())) {
                        intList.add(tbDepartment.getDepartmentId());
                    }
                }
                mubanMap.put("packageDepartidArr", intList);
                List<TbPackageMuban> packageMubans = packageMubanService.findAllPackageNoPageList(mubanMap);
                if (!packageMubans.isEmpty()) {
                    for (TbPackageMuban tbPackageMuban : packageMubans) {
                        if (!packageIds.contains(tbPackageMuban.getPackageId())) {
                            packageIds.add(tbPackageMuban.getPackageId());
                        }
                    }
                    map.put("packageIdArr", packageIds);
                } else {
                    packageIds.add("0");
                    map.put("packageIdArr", packageIds);
                }
            }
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                map.put("ren", "fafang");
                map.put("renArr", tiaomaList);
            }
            if (!packageMuban.isEmpty()) {
                for (TbPackageMuban tbPackageMuban : packageMuban) {
                    if (!intList.contains(tbPackageMuban.getPackageId())) {
                        intList.add(tbPackageMuban.getPackageId());
                    }
                }
                map.put("packageIdArr", intList);
            }
            if (department.isEmpty() && employee.isEmpty() && packageMuban.isEmpty()) {
                packageIds.add("0");
                map.put("packageIdArr", packageIds);
            }
        }
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        map.put("chaxun", "fafang");
        map.put("fafangtime", "fafangtime");
        map.put("fafangLiushui", "fafangLiushui");
        List<ZsPackageImformation> packageImformations = packageImformationService.tongjiChaxunTotal(map);
        //导出成excel表格
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("包发放统计报表");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("包条码");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("包名");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("包价格");
        cell.setCellStyle(style);
        cell = row.createCell((short) 3);
        cell.setCellValue("发放科室");
        cell.setCellStyle(style);
        cell = row.createCell((short) 4);
        cell.setCellValue("发放人");
        cell.setCellStyle(style);
        cell = row.createCell((short) 5);
        cell.setCellValue("发放时间");
        cell.setCellStyle(style);
        ZsPackageImformation packageImformation = null;
        Map<Float, Integer> countMap = new HashMap<Float, Integer>();//键为价格，值为数量
        Map<String, Float> packageMap = new HashMap<String, Float>();
        Object packagePrice;
        for (int i = 0; i < packageImformations.size(); i++) {
            packageImformation = packageImformations.get(i);
            if (packageMap.containsKey(packageImformation.getTbPackageMuban().getPackageName())) {
                //包含的时候数量加一
                packagePrice = packageMap.get(packageImformation.getTbPackageMuban().getPackageName());
                int k = countMap.get(packagePrice);
                k++;
                countMap.put(packageImformation.getTbPackageMuban().getPackagePrice(), k);
            }
            if (!packageMap.containsKey(packageImformation.getTbPackageMuban().getPackageName())) {
                packageMap.put(packageImformation.getTbPackageMuban().getPackageName(), packageImformation.getTbPackageMuban().getPackagePrice());
                //不包含，创建一个新的
                countMap.put(packageImformation.getTbPackageMuban().getPackagePrice(), 1);
            }
            row = sheet.createRow((int) i + 1);
            packageImformation = packageImformations.get(i);
            // 第四步，创建单元格，并设置值
            row.createCell((short) 0).setCellValue(packageImformation.getPackageTiaoma());
            row.createCell((short) 1).setCellValue(packageImformation.getTbPackageMuban().getPackageName());
            row.createCell((short) 2).setCellValue(packageImformation.getTbPackageMuban().getPackagePrice());
            if (packageImformation.getTbPackageMuban().getTbDepartment() != null) {
                row.createCell((short) 3).setCellValue(packageImformation.getTbPackageMuban().getTbDepartment().getDepartmentName());
            }
            if (packageImformation.getTbPackageMuban().getTbDepartment() == null) {
                row.createCell((short) 3).setCellValue("厂家");
            }
            row.createCell((short) 4).setCellValue(StartUpOnLoadData.emMap.get(packageImformation.getFafangrenTiaoma()));
            row.createCell((short) 5).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(packageImformation.getMiejunKaishiTime()));
        }
        row = sheet.createRow(packageImformations.size() + 1);
        row.createCell((short) 0).setCellValue("包总数:" + packageImformations.size());
        Float count = 0f;
        for (Map.Entry<Float, Integer> entry : countMap.entrySet()) {
            count = count + entry.getKey() * entry.getValue();
        }
        row.createCell((short) 1).setCellValue("总计费用：" + count + "元");
        try {
            String fileName = "包发放统计报表.xls";
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);// 指定下载的文件名
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //统计--一次品发放统计   （点击查询）
    @RequestMapping("/ycpfafangtongji.do")
    public ModelAndView ycpfafangtongji(String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Integer pageNo = request.getParameter("pageIndex") == null ? 1 : Integer.valueOf(request.getParameter("pageIndex"));
        Map<String, Object> map = new HashMap<String, Object>();
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        Map<String, Object> wupinMap = new HashMap<String, Object>();
        List<String> fafangList = new ArrayList<String>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        Date startTime = null;
        Date endTime = null;
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        List<String> intList = new ArrayList<String>();
        List<String> tiaomaList = new ArrayList<String>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<TbDepartment> department = departService.findDepartment(tiaojianMap);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            if (!department.isEmpty()) {
                for (TbDepartment tbDepartment : department) {
                    if (!intList.contains(tbDepartment.getDepartmentId())) {
                        intList.add(tbDepartment.getDepartmentId());
                    }
                }
                wupinMap.put("fangfangLeixing", 1);
                wupinMap.put("fangfaKeshiArr", intList);
                List<ZsWupinFafang> wupinFafangs = wupinfafangService.findAllfafangwupingNopage(wupinMap);
                if (!wupinFafangs.isEmpty()) {
                    for (ZsWupinFafang zsWupinFafang : wupinFafangs) {
                        if (!fafangList.contains(zsWupinFafang.getFafangLiushui())) {
                            fafangList.add(zsWupinFafang.getFafangLiushui());
                        }
                    }
                    map.put("fafangLiushuiArr", fafangList);
                } else {
                    fafangList.add("0");
                    map.put("fafangLiushuiArr", fafangList);
                }
            }
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                map.put("renArr", tiaomaList);
            }
            if (department.isEmpty() && employee.isEmpty()) {
                map.put("fafangrenTiaoma", "0");
            }
        }
        map.put("recordTime", "recordTime");
        map.put("pageNo", pageNo);
        map.put("pageSize", Const.PAGE_SIZE);
        PageInfo<ZsWupinYcpFafang> page = wupinYcpFafangSerivice.findAllYicipinNo(map);
        if (!page.getList().isEmpty()) {
            request.setAttribute("yicipiFafangs", page.getList());
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", page.getPages());
            request.setAttribute("emMap", StartUpOnLoadData.emMap);
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("totalNum", page.getTotal());
        } else {
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("yicipiFafangs", "");
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", 1);
            request.setAttribute("totalNum", 0);
        }
        return new ModelAndView("tongji/yicipin_tongji");
    }

    //统计--一次品发放统计   点击导出
    @RequestMapping("/ycpfafangdaochu.do")
    public void ycpfafangdaochu(String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Map<String, Object> map = new HashMap<String, Object>();
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        Map<String, Object> wupinMap = new HashMap<String, Object>();
        List<String> fafangList = new ArrayList<String>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        Date startTime = null;
        Date endTime = null;
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        List<String> tiaomaList = new ArrayList<String>();
        List<String> intList = new ArrayList<String>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<TbDepartment> department = departService.findDepartment(tiaojianMap);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            if (!department.isEmpty()) {
                for (TbDepartment tbDepartment : department) {
                    if (!intList.contains(tbDepartment.getDepartmentId())) {
                        intList.add(tbDepartment.getDepartmentId());
                    }
                }
                wupinMap.put("fangfangLeixing", 1);
                wupinMap.put("fangfaKeshi", intList);
                List<ZsWupinFafang> wupinFafangs = wupinfafangService.findAllfafangwupingNopage(wupinMap);
                if (!wupinFafangs.isEmpty()) {
                    for (ZsWupinFafang zsWupinFafang : wupinFafangs) {
                        if (!fafangList.contains(zsWupinFafang.getFafangLiushui())) {
                            fafangList.add(zsWupinFafang.getFafangLiushui());
                        }
                    }
                    map.put("fafangLiushuiArr", fafangList);
                } else {
                    fafangList.add("0");
                    map.put("fafangLiushuiArr", fafangList);
                }
            }
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                map.put("renArr", tiaomaList);
            }
            if (department.isEmpty() && employee.isEmpty()) {
                map.put("fafangrenTiaoma", "0");
            }
        }
        map.put("recordTime", "recordTime");
        List<ZsWupinYcpFafang> yicipiFafangs = wupinYcpFafangSerivice.findAllyicipin(map);
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("一次品发放统计报表");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("一次品名称");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("发放科室");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("发放数量");
        cell.setCellStyle(style);
        cell = row.createCell((short) 3);
        cell.setCellValue("发放人");
        cell.setCellStyle(style);
        cell = row.createCell((short) 4);
        cell.setCellValue("发放时间");
        cell.setCellStyle(style);
        ZsWupinYcpFafang wupinYcpFafang = null;
        for (int i = 0; i < yicipiFafangs.size(); i++) {
            row = sheet.createRow((int) i + 1);
            wupinYcpFafang = yicipiFafangs.get(i);
            // 第四步，创建单元格，并设置值
            row.createCell((short) 0).setCellValue(wupinYcpFafang.getYicipinTiaoma());
            if (wupinYcpFafang.getWupinFafang().getDepartment() != null) {
                row.createCell((short) 1).setCellValue(wupinYcpFafang.getWupinFafang().getDepartment().getDepartmentName());
            }
            if (wupinYcpFafang.getWupinFafang().getDepartment() == null) {
                row.createCell((short) 1).setCellValue("厂家");
            }
            row.createCell((short) 2).setCellValue(wupinYcpFafang.getYicipinShuliang());
            row.createCell((short) 3).setCellValue(StartUpOnLoadData.emMap.get(wupinYcpFafang.getFafangrenTiaoma()));
            row.createCell((short) 4).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(wupinYcpFafang.getRecordTime()));
        }
        row = sheet.createRow(yicipiFafangs.size() + 1);
        row.createCell((short) 0).setCellValue("统计总数:" + yicipiFafangs.size());
        try {
            String fileName = "一次品发放统计报表";
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);// 指定下载的文件名
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //统计--一次品入库统计   （点击查询）
    @RequestMapping("/ycprukutongji.do")
    public ModelAndView ycprukutongji(String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Integer pageNo = request.getParameter("pageIndex") == null ? 1 : Integer.valueOf(request.getParameter("pageIndex"));
        Map<String, Object> map = new HashMap<String, Object>();
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        Map<String, Object> rukuMap = new HashMap<String, Object>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        Date startTime = null;
        Date endTime = null;
        List<String> rukuLiushuiList = new ArrayList<>();
        List<String> tiaomaList = new ArrayList<String>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            List<ZsYicipinRukumingxi> yicipinRukumingxis = yicipinRukumingxiService.findAllNoPage(tiaojianMap);
            List<ZsYicipinRuku> yicipinRukus = yicipinRukuService.findAllYicipinNopage(tiaojianMap);
            if (!yicipinRukus.isEmpty()) {
                for (ZsYicipinRuku zsYicipinRuku : yicipinRukus) {
                    if (!rukuLiushuiList.contains(zsYicipinRuku.getRukuLiushui())) {
                        rukuLiushuiList.add(zsYicipinRuku.getRukuLiushui());
                    }
                }
                map.put("rukuLiushuiArr", rukuLiushuiList);
            }
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                rukuMap.put("reaArr", tiaomaList);
                yicipinRukus = yicipinRukuService.findAllYicipinNopage(rukuMap);
                for (ZsYicipinRuku zsYicipinRuku : yicipinRukus) {
                    if (!rukuLiushuiList.contains(zsYicipinRuku.getRukuLiushui())) {
                        rukuLiushuiList.add(zsYicipinRuku.getRukuLiushui());
                    }
                }
                map.put("rukuLiushuiArr", rukuLiushuiList);
            }
            if (!yicipinRukumingxis.isEmpty()) {
                map.put("tiaojian", tiaojian);
            }
            if (yicipinRukus.isEmpty() && employee.isEmpty() && yicipinRukumingxis.isEmpty()) {
                rukuLiushuiList.add("0");
                map.put("rukuLiushuiArr", rukuLiushuiList);
            }
        }
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        map.put("rukuCreatetime", "rukuCreatetime");
        map.put("pageNo", pageNo);
        map.put("pageSize", Const.PAGE_SIZE);
        PageInfo<ZsYicipinRukumingxi> page = yicipinRukumingxiService.findAllPage(map);
        if (!page.getList().isEmpty()) {
            request.setAttribute("yicipiRuku", page.getList());
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", page.getPages());
            request.setAttribute("emMap", StartUpOnLoadData.emMap);
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("totalNum", page.getTotal());
        } else {
            request.setAttribute("tiaojian", tiaojian);
            request.setAttribute("yicipiRuku", "");
            request.setAttribute("st", startTime);
            request.setAttribute("et", endTime);
            request.setAttribute("searchTxt", searchTxt == null ? "" : searchTxt);
            request.setAttribute("total", 1);
            request.setAttribute("totalNum", 0);
        }
        return new ModelAndView("tongji/yicipin_ruku");
    }

    //统计--一次品入库统计   点击导出
    @RequestMapping("/ycprukudaochu.do")
    public void ycprukudaochu(String tiaojian, String searchTxt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Map<String, Object> map = new HashMap<String, Object>();
        Map<String, Object> tiaojianMap = new HashMap<String, Object>();
        Map<String, Object> rukuMap = new HashMap<String, Object>();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        Date startTime = null;
        Date endTime = null;
        List<String> tiaomaList = new ArrayList<String>();
        List<String> rukuLiushuiList = new ArrayList<>();
        if (StringUtils.isNotBlank(tiaojian)) {
            tiaojianMap.put("tiaojian", tiaojian);
            List<TbEmployee> employee = employeeService.findEmployee(tiaojianMap);
            List<ZsYicipinRuku> yicipinRukus = yicipinRukuService.findAllYicipinNopage(tiaojianMap);
            List<ZsYicipinRukumingxi> yicipinRukumingxis = yicipinRukumingxiService.findAllNoPage(tiaojianMap);
            if (!yicipinRukus.isEmpty()) {
                for (ZsYicipinRuku zsYicipinRuku : yicipinRukus) {
                    if (!rukuLiushuiList.contains(zsYicipinRuku.getRukuLiushui())) {
                        rukuLiushuiList.add(zsYicipinRuku.getRukuLiushui());
                    }
                }
                map.put("rukuLiushuiArr", rukuLiushuiList);
            }
            if (!employee.isEmpty()) {
                for (TbEmployee tbEmployee : employee) {
                    if (!tiaomaList.contains(tbEmployee.getEmployeeNo())) {
                        tiaomaList.add(tbEmployee.getEmployeeNo());
                    }
                }
                rukuMap.put("reaArr", tiaomaList);
                yicipinRukus = yicipinRukuService.findAllYicipinNopage(rukuMap);
                for (ZsYicipinRuku zsYicipinRuku : yicipinRukus) {
                    if (!rukuLiushuiList.contains(zsYicipinRuku.getRukuLiushui())) {
                        rukuLiushuiList.add(zsYicipinRuku.getRukuLiushui());
                    }
                }
                map.put("rukuLiushuiArr", rukuLiushuiList);
            }
            if (!yicipinRukumingxis.isEmpty()) {
                map.put("tiaojian", tiaojian);
            }
            if (yicipinRukus.isEmpty() && employee.isEmpty() && yicipinRukumingxis.isEmpty()) {
                rukuLiushuiList.add("0");
                map.put("rukuLiushuiArr", rukuLiushuiList);
            }
        }
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        map.put("rukuCreatetime", "rukuCreatetime");
        List<ZsYicipinRukumingxi> rukumingxis = yicipinRukumingxiService.findAllNoPage(map);
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("一次品入库统计报表");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("一次品名称");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("发票号");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("入库人");
        cell.setCellStyle(style);
        cell = row.createCell((short) 3);
        cell.setCellValue("入库数量");
        cell.setCellStyle(style);
        cell = row.createCell((short) 4);
        cell.setCellValue("有效期(天)");
        cell.setCellStyle(style);
        cell = row.createCell((short) 5);
        cell.setCellValue("入库时间");
        cell.setCellStyle(style);
        ZsYicipinRukumingxi yicipinRukumingxi = null;
        for (int i = 0; i < rukumingxis.size(); i++) {
            row = sheet.createRow((int) i + 1);
            yicipinRukumingxi = rukumingxis.get(i);
            // 第四步，创建单元格，并设置值
            row.createCell((short) 0).setCellValue(yicipinRukumingxi.getProductName());
            row.createCell((short) 1).setCellValue(yicipinRukumingxi.getYicipinRuku().getRukuFapiaohao());
            row.createCell((short) 2).setCellValue(StartUpOnLoadData.emMap.get(yicipinRukumingxi.getYicipinRuku().getRukuRukurenTiaoma()));
            row.createCell((short) 3).setCellValue(yicipinRukumingxi.getProductNum());
            row.createCell((short) 4).setCellValue(yicipinRukumingxi.getProductYouxiaoqiDays());
            row.createCell((short) 5).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(yicipinRukumingxi.getRukuCreatetime()));
        }
        row = sheet.createRow(rukumingxis.size() + 1);
        row.createCell((short) 0).setCellValue("统计总数:" + rukumingxis.size());
        try {
            String fileName = "一次品入库统计报表";
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);// 指定下载的文件名
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @RequestMapping("/jifei.do")
    public ModelAndView jifei(HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        List<TbDepartment> departments = departService.findAllDepartment();
        request.setAttribute("department", departments);
        return new ModelAndView("/tongji/jifei/jifei");
    }

//    class Student2DateConverter implements WriteConvertible {
//
//
//        @Override
//        public Object execWrite(Object object) {
//
//            Date date = (Date) object;
//            return DateUtils.date2Str(date, DateUtils.DATE_FORMAT_MSEC_T_Z);
//        }
//    }


    public class CostCangjia{
        @ExcelField(title = "部门",order = 1)
        private String depName ;
        @ExcelField(title = "包名",order = 1)
        private String packageName;
        @ExcelField(title = "回收时间",order = 3)
        private String huishouTime;
        @ExcelField(title = "单价",order = 1)
        private double price;

        public int getNum() {
            return num;
        }

        public void setNum(int num) {
            this.num = num;
        }

        @ExcelField(title = "数量",order = 1)

        private int num;
        @ExcelField(title = "总价",order = 1)
        private double totlePrice;
        public String getHuishouTime() {
            return huishouTime;
        }

        public void setHuishouTime(String huishouTime) {
            this.huishouTime = huishouTime;
        }

        public String getDepName() {
            return depName;
        }

        public void setDepName(String depName) {
            this.depName = depName;
        }

        public String getPackageName() {
            return packageName;
        }

        public void setPackageName(String packageName) {
            this.packageName = packageName;
        }

        public double getPrice() {
            return price;
        }

        public void setPrice(double price) {
            this.price = price;
        }

        public double getTotlePrice() {
            return totlePrice;
        }

        public void setTotlePrice(double totlePrice) {
            this.totlePrice = totlePrice;
        }


    }

    @RequestMapping("/costBylinChuangTotle")
    public void costBylinChuangTotle(String startTime,String endTime,HttpServletRequest request,HttpServletResponse response)throws  Exception{
        Map<String,Object> map = new HashMap<>();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
        if(StringUtils.isNotBlank(startTime)&&StringUtils.isNotBlank(endTime)){
            Date start = simpleDateFormat.parse(startTime);
            Date end = simpleDateFormat.parse(endTime);
            map.put("startTime",start);
            map.put("endTime",end);
        }else{
            Date date = new Date();
            map.put("startTime",date);
            map.put("endTime",date);
        }
        map.put("departmentIsclean",0);
        List<TbDepartment> list = tbDepartmentService.findDepartment(map);
        List<CostCangjia> costCangjias = new ArrayList<>();
        for(TbDepartment tbDepartment : list){
            String departmentId = tbDepartment.getDepartmentId();
            map.put("depId",departmentId);
            List<ZsHuishouMingxi> zsHuishouMingxis = zsHuishouMingxiService.selectByCost(map);
            double totlePrice = 0;
            CostCangjia costCangjia = new CostCangjia();
            int totle = 0;
            for(ZsHuishouMingxi zsHuishouMingxi:zsHuishouMingxis){
                TbPackageMuban tbPackageMuban = zsHuishouMingxi.getTbPackageMuban();
                Float price = null;
                if(tbPackageMuban!=null){
                    price = tbPackageMuban.getPackagePrice();
                    if(price !=null){
                        totlePrice = totlePrice + price*zsHuishouMingxi.getPackageNum();
                    }
                }
                totle += zsHuishouMingxi.getPackageNum();
            }
            costCangjia.setDepName(tbDepartment.getDepartmentName());
            costCangjia.setTotlePrice(totlePrice);
            costCangjia.setNum(totle);
            costCangjias.add(costCangjia);
        }
        response.setContentType("application/octet-stream");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String name = "费用报表" + sdf.format(new Date()) + "-" + System.currentTimeMillis() + ".xlsx";
        response.setHeader(
                "content-disposition", "attachment;filename=" + new String(name.getBytes("UTF-8"), "iso-8859-1")
        );
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        ExcelUtils.getInstance().exportObjects2Excel(costCangjias, CostCangjia.class, true, null, true, os);
        byte[] bytes = os.toByteArray();
        ServletOutputStream outputStream = response.getOutputStream();
        outputStream.write(bytes);
        outputStream.close();

    }


    @RequestMapping("/costBylinChuangMingxi")
    public void costBylinChuangMingxi(String startTime,String endTime)throws  Exception{
        Map<String,Object> map = new HashMap<>();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
        if(StringUtils.isNotBlank(startTime)&&StringUtils.isNotBlank(endTime)){
            Date start = simpleDateFormat.parse(startTime);
            Date end = simpleDateFormat.parse(endTime);
            map.put("startTime",start);
            map.put("endTime",end);
        }else{
            Date date = new Date();
            map.put("startTime",date);
            map.put("endTime",date);
        }
        map.put("departmentIsclean",0);
        List<TbDepartment> list = tbDepartmentService.findDepartment(map);
        List<CostCangjia> costCangjias = new ArrayList<>();

        for(TbDepartment tbDepartment : list){
            String departmentId = tbDepartment.getDepartmentId();

            map.put("depId",departmentId);
            List<ZsHuishouMingxi> zsHuishouMingxis = zsHuishouMingxiService.selectByCost(map);
            double totlePrice = 0;
            int count = 0;
            for(ZsHuishouMingxi zsHuishouMingxi:zsHuishouMingxis){
                count++;
                TbPackageMuban tbPackageMuban = zsHuishouMingxi.getTbPackageMuban();
                Float price = null;
                CostCangjia costCangjia = new CostCangjia();
                if(tbPackageMuban!=null){
                    price = tbPackageMuban.getPackagePrice();
                    if(price !=null){
                        totlePrice = totlePrice + price*zsHuishouMingxi.getPackageNum();
                        costCangjia.setPrice(price);
                    }
                }
                costCangjia.setDepName(tbDepartment.getDepartmentName());
                costCangjia.setPackageName(zsHuishouMingxi.getHuishouBaoname());
                costCangjia.setNum(zsHuishouMingxi.getPackageNum());
                costCangjia.setTotlePrice(totlePrice);
                Date date = zsHuishouMingxi.getHuishouTime();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String huishouTime = sdf.format(date);
                costCangjia.setHuishouTime(huishouTime);
                costCangjias.add(costCangjia);
            }
        }
        response.setContentType("application/octet-stream");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String name = "费用明细报表" + sdf.format(new Date()) + "-" + System.currentTimeMillis() + ".xlsx";
        response.setHeader(
                "content-disposition", "attachment;filename=" + new String(name.getBytes("UTF-8"), "iso-8859-1")
        );
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        ExcelUtils.getInstance().exportObjects2Excel(costCangjias, CostCangjia.class, true, null, true, os);
        byte[] bytes = os.toByteArray();
        ServletOutputStream outputStream = response.getOutputStream();
        outputStream.write(bytes);
        outputStream.close();

//        ExcelUtils.getInstance().exportObjects2Excel(costCangjias,CostCangjia.class,true,null,true,header,"D:/D.xlsx");

    }

    @RequestMapping("/costBycangjia")
    public void costDepartmentAll(String startTime ,String endTime) throws Exception {
        Map<String,Object> map = new HashMap<>();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
        if(StringUtils.isNotBlank(startTime)&&StringUtils.isNotBlank(endTime)){
            Date start = simpleDateFormat.parse(startTime);
            Date end = simpleDateFormat.parse(endTime);
            map.put("startTime",start);
            map.put("endTime",end);
        }else{
            Date date = new Date();
            map.put("startTime",date);
            map.put("endTime",date);
        }
        map.put("departmentIsclean",1);
        List<TbDepartment> list = tbDepartmentService.findDepartment(map);
        List<CostCangjia> costCangjias = new ArrayList<>();
        List<String> header = new ArrayList<>();
        for(TbDepartment tbDepartment : list){
            String departmentId = tbDepartment.getDepartmentId();
            map.put("depId",departmentId);
            List<ZsHuishouMingxi> zsHuishouMingxis = zsHuishouMingxiService.selectByCost(map);
            double totlePrice = 0;
            int count = 0;
            for(ZsHuishouMingxi zsHuishouMingxi:zsHuishouMingxis){
                count++;
                TbPackageMuban tbPackageMuban = zsHuishouMingxi.getTbPackageMuban();
                Float price = null;
                CostCangjia costCangjia = new CostCangjia();
                if(tbPackageMuban!=null){
                    price = tbPackageMuban.getPackagePrice();
                    if(price !=null){

                        totlePrice = totlePrice + price*1;
                        costCangjia.setPrice(price);
                    }
                }
                costCangjia.setDepName(tbDepartment.getDepartmentName());
                costCangjia.setPackageName(zsHuishouMingxi.getHuishouBaoname());
                costCangjia.setNum(1);
                costCangjia.setTotlePrice(totlePrice);
                Date date = zsHuishouMingxi.getHuishouTime();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String huishouTime = sdf.format(date);
                costCangjia.setHuishouTime(huishouTime);
                costCangjias.add(costCangjia);
            }
        }
        response.setContentType("application/octet-stream");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String name = "厂家明细报表" + sdf.format(new Date()) + "-" + System.currentTimeMillis() + ".xlsx";
        response.setHeader(
                "content-disposition", "attachment;filename=" + new String(name.getBytes("UTF-8"), "iso-8859-1")
        );
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        ExcelUtils.getInstance().exportObjects2Excel(costCangjias, CostCangjia.class, true, null, true, os);
        byte[] bytes = os.toByteArray();
        ServletOutputStream outputStream = response.getOutputStream();
        outputStream.write(bytes);
        outputStream.close();
//        ExcelUtils.getInstance().exportObjects2Excel(costCangjias,CostCangjia.class,true,null,true,header,"D:/D.xlsx");
    }

    //统计--计费统计   （点击查询）
    //根据科室和时间统计费用
    @RequestMapping("/jifeitongji.do")
    public ModelAndView jifeitongji(String txt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Map<String, Object> map = new HashMap<>();
        Map<String, PinmingNumVO> wujunMap = new HashMap<>();
        Map<String, PinmingNumVO> qingjieMap = new HashMap<>();
        Map<String, PinmingNumVO> zhouzhuanMap = new HashMap<>();
        Map<String, PinmingNumVO> chengbenMap = new HashMap<>();
        Map<String, PinmingNumVO> queshiMap = new HashMap<>();
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        Date startTime = null;
        Date endTime = null;
        PinmingNumVO pnVO = null;
        if (StringUtils.isNotBlank(st)) {

            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        Float wujunTotal = 0f;
        Float qingjieTotal = 0f;
        List<String> fafangliushuis = new ArrayList<>();
        List<String> shenqingliushuis = new ArrayList<>();
        TbDepartment department = null;
        if (StringUtils.isNotBlank(txt)) {
            map.put("keshiName", txt);
            department = departService.selectByPrimaryKey(txt);
        } else {
            map.put("keshiName", "null");
        }
        if (department != null) {
            map.put("fangfaKeshi", department.getDepartmentId());
            map.put("shenqingKeshi", department.getDepartmentId());
            map.put("shifouXuyao", 1);
            map.put("keshiId", department.getDepartmentId());

            List<ZsWupinFafang> wupinFafangs = wupinfafangService.findAllfafangwupingNopage(map);
            List<ZsShenqing> sheqingList = shenqingService.findAllNoPage(map);
            List<ZsKeshiKucun> keshiKucuns = kucunService.findAllJifei(map);
            for (ZsWupinFafang zsWupinFafang : wupinFafangs) {
                if (!fafangliushuis.contains(zsWupinFafang.getFafangLiushui())) {
                    fafangliushuis.add(zsWupinFafang.getFafangLiushui());
                }
            }
            if (!fafangliushuis.isEmpty()) {
                map.put("fafangLiushuiArr", fafangliushuis);
            }
            if (fafangliushuis.isEmpty()) {
                fafangliushuis.add("0");
                map.put("fafangLiushuiArr", fafangliushuis);
            }
            for (ZsShenqing zsShenqing : sheqingList) {
                if (!shenqingliushuis.contains(zsShenqing.getShenqingLiushui())) {
                    shenqingliushuis.add(zsShenqing.getShenqingLiushui());
                }
            }
            if (!shenqingliushuis.isEmpty()) {
                map.put("shenqingLiushuiArr", shenqingliushuis);
            }
            if (shenqingliushuis.isEmpty()) {
                shenqingliushuis.add("0");
                map.put("shenqingLiushuiArr", shenqingliushuis);
            }
            ZsYicipinKucun yicipinKucun = null;
            Float totalPrice = 0f;
            Float xinzeng = 0f;
            List<ZsWupinYcpFafang> ycpFafangs = wupinYcpFafangSerivice.findAllyicipin(map);
            List<ZsWupinBaoFafang> wupinBaoFafangs = wupinbaoFafangService.findAllWupinbao(map);
            List<ZsShenqingShoushubaoMingxi> ssbshenqingMingxis = ssbshenqingService.findAllShoushubaoApplyDetail(map);
            //此处是对发放的一次品的统计
            for (ZsWupinYcpFafang zsWupinYcpFafang : ycpFafangs) {
                yicipinKucun = ycpKuncunService.selectByPrimaryKey(zsWupinYcpFafang.getYicipinTiaoma());
                pnVO = new PinmingNumVO();
                //一次无菌物品
                if (yicipinKucun.getProductType() == 0) {
                    if (wujunMap.containsKey(zsWupinYcpFafang.getYicipinTiaoma())) {
                        //包括时只增加数据
                        xinzeng = zsWupinYcpFafang.getYicipinShuliang() * yicipinKucun.getProductPrice();
                        pnVO = wujunMap.get(zsWupinYcpFafang.getYicipinTiaoma());
                        pnVO.setNum(pnVO.getNum() + zsWupinYcpFafang.getYicipinShuliang());
                        pnVO.setTotalPrice(pnVO.getTotalPrice() + xinzeng);
                        pnVO.setProType(yicipinKucun.getProductType());
                        wujunMap.put(zsWupinYcpFafang.getYicipinTiaoma(), pnVO);
                        wujunTotal = wujunTotal + xinzeng;
                    }
                    if (!wujunMap.containsKey(zsWupinYcpFafang.getYicipinTiaoma())) {
                        //不包括时新增一组
                        totalPrice = yicipinKucun.getProductPrice() * zsWupinYcpFafang.getYicipinShuliang();
                        pnVO.setProductName(yicipinKucun.getProductName());
                        pnVO.setNum(zsWupinYcpFafang.getYicipinShuliang());
                        pnVO.setProductPrice(yicipinKucun.getProductPrice());
                        pnVO.setTotalPrice(totalPrice);
                        pnVO.setProType(yicipinKucun.getProductType());
                        wujunTotal = wujunTotal + totalPrice;
                        wujunMap.put(zsWupinYcpFafang.getYicipinTiaoma(), pnVO);
                    }
                }
                //一次清洁物品
                if (yicipinKucun.getProductType() == 1) {
                    if (qingjieMap.containsKey(zsWupinYcpFafang.getYicipinTiaoma())) {
                        //包括时只增加数据
                        xinzeng = zsWupinYcpFafang.getYicipinShuliang() * yicipinKucun.getProductPrice();
                        pnVO = qingjieMap.get(zsWupinYcpFafang.getYicipinTiaoma());
                        pnVO.setNum(pnVO.getNum() + zsWupinYcpFafang.getYicipinShuliang());
                        pnVO.setTotalPrice(pnVO.getTotalPrice() + xinzeng);
                        pnVO.setProType(yicipinKucun.getProductType());
                        qingjieMap.put(zsWupinYcpFafang.getYicipinTiaoma(), pnVO);
                        qingjieTotal = qingjieTotal + xinzeng;
                    }
                    if (!qingjieMap.containsKey(zsWupinYcpFafang.getYicipinTiaoma())) {
                        //不包括时新增一组
                        totalPrice = yicipinKucun.getProductPrice() * zsWupinYcpFafang.getYicipinShuliang();
                        pnVO.setProductName(yicipinKucun.getProductName());
                        pnVO.setNum(zsWupinYcpFafang.getYicipinShuliang());
                        pnVO.setProductPrice(yicipinKucun.getProductPrice());
                        pnVO.setTotalPrice(totalPrice);
                        pnVO.setProType(yicipinKucun.getProductType());
                        qingjieTotal = xinzeng + totalPrice;
                        qingjieMap.put(zsWupinYcpFafang.getYicipinTiaoma(), pnVO);
                    }
                }
            }
            ZsPackageImformation packageImformation = null;
            TbPackageMuban packageMuban = null;
            //此处是对包的发放的统计(只针对处理费用)
            for (ZsWupinBaoFafang zsWupinBaoFafang : wupinBaoFafangs) {
                pnVO = new PinmingNumVO();
                packageImformation = zsWupinBaoFafang.getPackageImformation();
                packageMuban = packageImformation.getTbPackageMuban();
                if (packageMuban != null) {
                    if (zhouzhuanMap.containsKey(packageMuban.getPackageId())) {
                        //已存在该模板包
                        xinzeng = packageMuban.getPackagePrice();
                        pnVO = zhouzhuanMap.get(packageMuban.getPackageId());
                        pnVO.setNum(pnVO.getNum() + 1);
                        pnVO.setProductPrice(packageMuban.getPackagePrice());
                        if (pnVO.getTotalPrice() != null) {
                            totalPrice = pnVO.getTotalPrice() + xinzeng;
                            pnVO.setTotalPrice(totalPrice);
                            zhouzhuanMap.put(packageMuban.getPackageId(), pnVO);
                        }

                    }
                    if (!zhouzhuanMap.containsKey(packageMuban.getPackageId())) {
                        //新增一个模板包
                        pnVO.setNum(1);
                        pnVO.setProductName(packageMuban.getPackageName());
                        pnVO.setProductPrice(packageMuban.getPackagePrice());
                        totalPrice = packageMuban.getPackagePrice();
                        pnVO.setTotalPrice(totalPrice);
                        zhouzhuanMap.put(packageMuban.getPackageId(), pnVO);
                    }
                }
            }
            for (ZsShenqingShoushubaoMingxi zsShenqingShoushubaoMingxi : ssbshenqingMingxis) {
                pnVO = new PinmingNumVO();
                if (chengbenMap.containsKey(zsShenqingShoushubaoMingxi.getPackageMuban())) {
                    if (packageMuban.getPackagePrice() != null) {
                        xinzeng = packageMuban.getPackagePrice();
                        pnVO = chengbenMap.get(zsShenqingShoushubaoMingxi.getShenqingWupinbaoMuban());
                        pnVO.setNum(pnVO.getNum() + zsShenqingShoushubaoMingxi.getShenqingWupinbaoShuliang());
                        pnVO.setProductPrice(zsShenqingShoushubaoMingxi.getPackageMuban().getPackageChengben());
                        totalPrice = pnVO.getTotalPrice() + xinzeng;
                        pnVO.setTotalPrice(totalPrice);
                        chengbenMap.put(zsShenqingShoushubaoMingxi.getShenqingWupinbaoMuban(), pnVO);
                    }

                }
                if (!chengbenMap.containsKey(zsShenqingShoushubaoMingxi.getPackageMuban())) {
                    pnVO.setNum(zsShenqingShoushubaoMingxi.getShenqingWupinbaoShuliang());
                    pnVO.setProductName(zsShenqingShoushubaoMingxi.getPackageMuban().getPackageName());
                    pnVO.setProductPrice(zsShenqingShoushubaoMingxi.getPackageMuban().getPackageChengben());
                    totalPrice = (zsShenqingShoushubaoMingxi.getShenqingWupinbaoShuliang()) * (zsShenqingShoushubaoMingxi.getPackageMuban().getPackageChengben());
                    pnVO.setTotalPrice(totalPrice);
                    chengbenMap.put(zsShenqingShoushubaoMingxi.getShenqingWupinbaoMuban(), pnVO);
                }
            }
            for (ZsKeshiKucun keshiKucun : keshiKucuns) {
                pnVO = new PinmingNumVO();
                if (keshiKucun.getKucunSort() == 1) {
                    if (queshiMap.containsKey(keshiKucun.getMubanId())) {
                        xinzeng = packageMuban.getPackagePrice();
                        pnVO = queshiMap.get(keshiKucun.getMubanId());
                        pnVO.setNum(pnVO.getNum() + keshiKucun.getDiushiKucun());
                        pnVO.setProductPrice(keshiKucun.getPackageMuban().getPackageChengben());
                        totalPrice = pnVO.getTotalPrice() + xinzeng;
                        pnVO.setTotalPrice(totalPrice);
                        queshiMap.put(keshiKucun.getMubanId(), pnVO);
                    }
                    if (!queshiMap.containsKey(keshiKucun.getMubanId())) {
                        pnVO.setNum(keshiKucun.getDiushiKucun());
                        pnVO.setProductName(keshiKucun.getPackageMuban().getPackageName());
                        pnVO.setProductPrice(keshiKucun.getPackageMuban().getPackageChengben());
                        totalPrice = (keshiKucun.getDiushiKucun()) * (keshiKucun.getPackageMuban().getPackageChengben());
                        pnVO.setTotalPrice(totalPrice);
                        queshiMap.put(keshiKucun.getMubanId(), pnVO);
                    }
                }
            }
            //合计费用
            Float wujunHeji = 0f;
            Float qingjieHeji = 0f;
            Float zhouzhuanHeji = 0f;
            Float chengbenHeji = 0f;
            Float queshiHeji = 0f;
            //各个项目的行数
            int wujunRow = wujunMap.size();
            int qingjieRow = qingjieMap.size();
            int zhouzhuanRow = zhouzhuanMap.size();
            int chengbenRow = chengbenMap.size();
            int queshiRow = chengbenMap.size();
            for (Map.Entry<String, PinmingNumVO> entry : wujunMap.entrySet()) {
                if (entry.getValue().getTotalPrice() != null) {
                    wujunHeji = wujunHeji + entry.getValue().getTotalPrice();
                }

            }
            for (Map.Entry<String, PinmingNumVO> entry : qingjieMap.entrySet()) {
                if (entry.getValue().getTotalPrice() != null) {
                    qingjieHeji = qingjieHeji + entry.getValue().getTotalPrice();
                }

            }
            for (Map.Entry<String, PinmingNumVO> entry : chengbenMap.entrySet()) {
                if (entry.getValue().getTotalPrice() != null) {
                    chengbenHeji = chengbenHeji + entry.getValue().getTotalPrice();
                }

            }
            for (Map.Entry<String, PinmingNumVO> entry : zhouzhuanMap.entrySet()) {
                if (entry.getValue().getTotalPrice() != null) {
                    zhouzhuanHeji = zhouzhuanHeji + entry.getValue().getTotalPrice();
                }

            }
            for (Map.Entry<String, PinmingNumVO> entry : queshiMap.entrySet()) {
                if (entry.getValue().getTotalPrice() != null) {
                    queshiHeji = queshiHeji + entry.getValue().getTotalPrice();
                }
            }
            request.setAttribute("wujunRow", wujunRow);
            request.setAttribute("qingjieRow", qingjieRow);
            request.setAttribute("zhouzhuanRow", zhouzhuanRow);
            request.setAttribute("zhouzhuanRow", chengbenRow);
            request.setAttribute("queshiHeji", queshiHeji);
            request.setAttribute("chengbenTotal", chengbenHeji);
            request.setAttribute("wujunTotal", wujunHeji);
            request.setAttribute("qingjieTotal", qingjieHeji);
            request.setAttribute("zhouzhuanTotal", zhouzhuanHeji);
            request.setAttribute("queshiTotal", queshiHeji);
            request.setAttribute("zhouzhuanMap", zhouzhuanMap);
            request.setAttribute("qingjieMap", qingjieMap);
            request.setAttribute("wujunMap", wujunMap);
            request.setAttribute("chengbenMap", chengbenMap);
            request.setAttribute("queshiMap", queshiMap);
        }
        List<TbDepartment> departments = departService.findAllDepartment();
        request.setAttribute("department", departments);
        request.setAttribute("st", startTime);
        request.setAttribute("et", endTime);
        request.setAttribute("keshi", txt);
        request.setAttribute("dep", department);
        return new ModelAndView("tongji/jifei/jifei");
    }

    //统计--计费    点击导出
    @RequestMapping("/jifeidaochu.do")
    public void jifeidaochu(String txt, HttpServletRequest request, HttpServletResponse response) throws BusinessException, ParseException {
        Map<String, Object> map = new HashMap<String, Object>();
        Map<String, PinmingNumVO> wujunMap = new HashMap<>();
        Map<String, PinmingNumVO> qingjieMap = new HashMap<>();
        Map<String, PinmingNumVO> zhouzhuanMap = new HashMap<>();
        Map<String, PinmingNumVO> chengbenMap = new HashMap<>();
        Map<String, PinmingNumVO> queshiMap = new HashMap<>();
        String st = request.getParameter("startTime");
        String et = request.getParameter("endTime");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        int totalRow = 0;
        int wujunRow = 0;
        int qingjieRow = 0;
        int chengbenRow = 0;
        int zhouzhuanRow = 0;
        int queshiRow = 0;
        Float wujunHeji = 0f;
        Float qingjieHeji = 0f;
        Float zhouzhuanHeji = 0f;
        Float chengbenHeji = 0f;
        Float queshiHeji = 0f;
        Date startTime = null;
        Date endTime = null;
        PinmingNumVO pnVO = null;
        if (StringUtils.isNotBlank(st)) {
            startTime = sdf.parse(st);
            map.put("startTime", startTime);
        }
        if (StringUtils.isNotBlank(et)) {
            endTime = sdf.parse(et);
            map.put("endTime", endTime);
        }
        Float wujunTotal = 0f;
        Float qingjieTotal = 0f;
        List<String> fafangliushuis = new ArrayList<String>();
        List<String> shenqingliushuis = new ArrayList<String>();
        TbDepartment department = null;
        if (StringUtils.isNotBlank(txt)) {
            map.put("keshiName", txt);
            department = departService.selectByPrimaryKey(txt);
        } else {
            map.put("keshiName", "null");
        }
        if (department != null) {
            map.put("fangfaKeshi", department.getDepartmentId());
            map.put("shenqingKeshi", department.getDepartmentId());
            map.put("shifouXuyao", 1);
            map.put("keshiId", department.getDepartmentId());
            List<ZsWupinFafang> wupinFafangs = wupinfafangService.findAllfafangwupingNopage(map);
            List<ZsShenqing> sheqingList = shenqingService.findAllNoPage(map);
            List<ZsKeshiKucun> keshiKucuns = kucunService.findAllJifei(map);
            for (ZsWupinFafang zsWupinFafang : wupinFafangs) {
                if (!fafangliushuis.contains(zsWupinFafang.getFafangLiushui())) {
                    fafangliushuis.add(zsWupinFafang.getFafangLiushui());
                }
            }
            if (!fafangliushuis.isEmpty()) {
                map.put("fafangLiushuiArr", fafangliushuis);
            }
            if (fafangliushuis.isEmpty()) {
                fafangliushuis.add("0");
                map.put("fafangLiushuiArr", fafangliushuis);

            }
            for (ZsShenqing zsShenqing : sheqingList) {
                if (!shenqingliushuis.contains(zsShenqing.getShenqingLiushui())) {
                    shenqingliushuis.add(zsShenqing.getShenqingLiushui());
                }
            }
            if (!shenqingliushuis.isEmpty()) {
                map.put("shenqingLiushuiArr", shenqingliushuis);
            }
            if (shenqingliushuis.isEmpty()) {
                shenqingliushuis.add("0");
                map.put("shenqingLiushuiArr", shenqingliushuis);
            }
            ZsYicipinKucun yicipinKucun = null;
            Float totalPrice = 0f;
            Float xinzeng = 0f;
            List<ZsWupinYcpFafang> ycpFafangs = wupinYcpFafangSerivice.findAllyicipin(map);
            List<ZsWupinBaoFafang> wupinBaoFafangs = wupinbaoFafangService.findAllWupinbao(map);
            List<ZsShenqingShoushubaoMingxi> ssbshenqingMingxis = ssbshenqingService.findAllShoushubaoApplyDetail(map);
            //此处是对发放的一次品的统计
            for (ZsWupinYcpFafang zsWupinYcpFafang : ycpFafangs) {
                yicipinKucun = ycpKuncunService.selectByPrimaryKey(zsWupinYcpFafang.getYicipinTiaoma());
                pnVO = new PinmingNumVO();
                //一次无菌物品
                if (yicipinKucun.getProductType() == 0) {
                    if (wujunMap.containsKey(zsWupinYcpFafang.getYicipinTiaoma())) {
                        //包括时只增加数量
                        xinzeng = zsWupinYcpFafang.getYicipinShuliang() * yicipinKucun.getProductPrice();
                        pnVO = wujunMap.get(zsWupinYcpFafang.getYicipinTiaoma());
                        pnVO.setNum(pnVO.getNum() + zsWupinYcpFafang.getYicipinShuliang());
                        pnVO.setTotalPrice(pnVO.getTotalPrice() + xinzeng);
                        pnVO.setProType(yicipinKucun.getProductType());
                        wujunMap.put(zsWupinYcpFafang.getYicipinTiaoma(), pnVO);
                        wujunTotal = wujunTotal + xinzeng;
                    }
                    if (!wujunMap.containsKey(zsWupinYcpFafang.getYicipinTiaoma())) {
                        //不包括时新增一组
                        totalPrice = yicipinKucun.getProductPrice() * zsWupinYcpFafang.getYicipinShuliang();
                        pnVO.setProductName(yicipinKucun.getProductName());
                        pnVO.setNum(zsWupinYcpFafang.getYicipinShuliang());
                        pnVO.setProductPrice(yicipinKucun.getProductPrice());
                        pnVO.setTotalPrice(totalPrice);
                        pnVO.setProType(yicipinKucun.getProductType());
                        wujunTotal = wujunTotal + totalPrice;
                        wujunMap.put(zsWupinYcpFafang.getYicipinTiaoma(), pnVO);
                    }
                }
                //一次清洁物品
                if (yicipinKucun.getProductType() == 1) {
                    if (qingjieMap.containsKey(zsWupinYcpFafang.getYicipinTiaoma())) {
                        //包括时只增加数据
                        xinzeng = zsWupinYcpFafang.getYicipinShuliang() * yicipinKucun.getProductPrice();
                        pnVO = qingjieMap.get(zsWupinYcpFafang.getYicipinTiaoma());
                        pnVO.setNum(pnVO.getNum() + zsWupinYcpFafang.getYicipinShuliang());
                        pnVO.setTotalPrice(pnVO.getTotalPrice() + xinzeng);
                        pnVO.setProType(yicipinKucun.getProductType());
                        qingjieMap.put(zsWupinYcpFafang.getYicipinTiaoma(), pnVO);
                        qingjieTotal = qingjieTotal + xinzeng;
                    }
                    if (!qingjieMap.containsKey(zsWupinYcpFafang.getYicipinTiaoma())) {
                        //不包括时新增一组
                        totalPrice = yicipinKucun.getProductPrice() * zsWupinYcpFafang.getYicipinShuliang();
                        pnVO.setProductName(yicipinKucun.getProductName());
                        pnVO.setNum(zsWupinYcpFafang.getYicipinShuliang());
                        pnVO.setProductPrice(yicipinKucun.getProductPrice());
                        pnVO.setTotalPrice(totalPrice);
                        pnVO.setProType(yicipinKucun.getProductType());
                        qingjieTotal = xinzeng + totalPrice;
                        qingjieMap.put(zsWupinYcpFafang.getYicipinTiaoma(), pnVO);
                    }
                }
            }
            ZsPackageImformation packageImformation = null;
            TbPackageMuban packageMuban = null;
            //此处是对包的发放的统计(只针对处理费用,也就是周转费)
            for (ZsWupinBaoFafang zsWupinBaoFafang : wupinBaoFafangs) {
                pnVO = new PinmingNumVO();
                packageImformation = zsWupinBaoFafang.getPackageImformation();
                packageMuban = packageImformation.getTbPackageMuban();
                if (packageMuban != null) {
                    if (zhouzhuanMap.containsKey(packageMuban.getPackageId())) {
                        //已存在该模板包
                        if (packageMuban.getPackagePrice() != null) {
                            xinzeng = packageMuban.getPackagePrice();
                            pnVO = zhouzhuanMap.get(packageMuban.getPackageId());
                            pnVO.setNum(pnVO.getNum() + 1);
                            pnVO.setProductPrice(packageMuban.getPackagePrice());
                            totalPrice = pnVO.getTotalPrice() + xinzeng;
                            pnVO.setTotalPrice(totalPrice);
                            zhouzhuanMap.put(packageMuban.getPackageId(), pnVO);
                        }
                    }
                    if (!zhouzhuanMap.containsKey(packageMuban.getPackageId())) {
                        //新增一个模板包
                        pnVO.setNum(1);
                        pnVO.setProductName(packageMuban.getPackageName());
                        pnVO.setProductPrice(packageMuban.getPackagePrice());
                        totalPrice = packageMuban.getPackagePrice();
                        pnVO.setTotalPrice(totalPrice);
                        zhouzhuanMap.put(packageMuban.getPackageId(), pnVO);
                    }
                }

            }
            //此处是对无菌包的成本费的计算
            for (ZsShenqingShoushubaoMingxi zsShenqingShoushubaoMingxi : ssbshenqingMingxis) {
                pnVO = new PinmingNumVO();
                if (chengbenMap.containsKey(zsShenqingShoushubaoMingxi.getPackageMuban())) {
                    xinzeng = packageMuban.getPackagePrice();
                    pnVO = chengbenMap.get(zsShenqingShoushubaoMingxi.getShenqingWupinbaoMuban());
                    pnVO.setNum(pnVO.getNum() + zsShenqingShoushubaoMingxi.getShenqingWupinbaoShuliang());
                    pnVO.setProductPrice(zsShenqingShoushubaoMingxi.getPackageMuban().getPackageChengben());
                    totalPrice = pnVO.getTotalPrice() + xinzeng;
                    pnVO.setTotalPrice(totalPrice);
                    chengbenMap.put(zsShenqingShoushubaoMingxi.getShenqingWupinbaoMuban(), pnVO);
                }
                if (!chengbenMap.containsKey(zsShenqingShoushubaoMingxi.getPackageMuban())) {
                    pnVO.setNum(zsShenqingShoushubaoMingxi.getShenqingWupinbaoShuliang());
                    pnVO.setProductName(zsShenqingShoushubaoMingxi.getPackageMuban().getPackageName());
                    pnVO.setProductPrice(zsShenqingShoushubaoMingxi.getPackageMuban().getPackageChengben());
                    totalPrice = zsShenqingShoushubaoMingxi.getShenqingWupinbaoShuliang() * zsShenqingShoushubaoMingxi.getPackageMuban().getPackageChengben();
                    pnVO.setTotalPrice(totalPrice);
                    chengbenMap.put(zsShenqingShoushubaoMingxi.getShenqingWupinbaoMuban(), pnVO);
                }
            }
            //缺失包的费用计算
            for (ZsKeshiKucun keshiKucun : keshiKucuns) {
                pnVO = new PinmingNumVO();
                if (keshiKucun.getKucunSort() == 1) {
                    if (queshiMap.containsKey(keshiKucun.getMubanId())) {
                        xinzeng = packageMuban.getPackagePrice();
                        pnVO = queshiMap.get(keshiKucun.getMubanId());
                        pnVO.setNum(pnVO.getNum() + keshiKucun.getDiushiKucun());
                        pnVO.setProductPrice(keshiKucun.getPackageMuban().getPackageChengben());
                        totalPrice = pnVO.getTotalPrice() + xinzeng;
                        pnVO.setTotalPrice(totalPrice);
                        queshiMap.put(keshiKucun.getMubanId(), pnVO);
                    }
                    if (!queshiMap.containsKey(keshiKucun.getMubanId())) {
                        pnVO.setNum(keshiKucun.getDiushiKucun());
                        pnVO.setProductName(keshiKucun.getPackageMuban().getPackageName());
                        pnVO.setProductPrice(keshiKucun.getPackageMuban().getPackageChengben());
                        totalPrice = (keshiKucun.getDiushiKucun()) * (keshiKucun.getPackageMuban().getPackageChengben());
                        pnVO.setTotalPrice(totalPrice);
                        queshiMap.put(keshiKucun.getMubanId(), pnVO);
                    }
                }
            }
            if (wujunMap.isEmpty()) {
                wujunRow = 1;
            }
            if (!wujunMap.isEmpty()) {
                wujunRow = wujunMap.size();
            }
            if (qingjieMap.isEmpty()) {
                qingjieRow = 1;
            }
            if (!qingjieMap.isEmpty()) {
                qingjieRow = qingjieMap.size();
            }
            if (chengbenMap.isEmpty()) {
                chengbenRow = 1;
            }
            if (!chengbenMap.isEmpty()) {
                chengbenRow = chengbenMap.size();
            }
            if (zhouzhuanMap.isEmpty()) {
                zhouzhuanRow = 1;
            }
            if (!zhouzhuanMap.isEmpty()) {
                zhouzhuanRow = zhouzhuanMap.size();
            }
            if (queshiMap.isEmpty()) {
                queshiRow = 1;
            }
            if (!queshiMap.isEmpty()) {
                queshiRow = queshiMap.size();
            }
            for (Map.Entry<String, PinmingNumVO> entry : wujunMap.entrySet()) {
                if (entry.getValue().getTotalPrice() != null) {
                    wujunHeji = wujunHeji + entry.getValue().getTotalPrice();
                }

            }
            for (Map.Entry<String, PinmingNumVO> entry : qingjieMap.entrySet()) {
                if (entry.getValue().getTotalPrice() != null) {
                    qingjieHeji = qingjieHeji + entry.getValue().getTotalPrice();
                }

            }
            for (Map.Entry<String, PinmingNumVO> entry : chengbenMap.entrySet()) {
                if (entry.getValue().getTotalPrice() != null) {
                    chengbenHeji = chengbenHeji + entry.getValue().getTotalPrice();
                }
            }
            for (Map.Entry<String, PinmingNumVO> entry : zhouzhuanMap.entrySet()) {
                if (entry.getValue().getTotalPrice() != null) {
                    zhouzhuanHeji = zhouzhuanHeji + entry.getValue().getTotalPrice();
                }
            }
            for (Map.Entry<String, PinmingNumVO> entry : queshiMap.entrySet()) {
                if (entry.getValue().getTotalPrice() != null) {
                    queshiHeji = queshiHeji + entry.getValue().getTotalPrice();
                }

            }
        }
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(department.getDepartmentName() + "计费统计报表");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue("科室");
        cell.setCellStyle(style);
        cell = row.createCell((short) 1);
        cell.setCellValue("项目");
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);
        cell.setCellValue("品名");
        cell.setCellStyle(style);
        cell = row.createCell((short) 3);
        cell.setCellValue("数量");
        cell.setCellStyle(style);
        cell = row.createCell((short) 4);
        cell.setCellValue("单价(元)");
        cell.setCellStyle(style);
        cell = row.createCell((short) 5);
        cell.setCellValue("总价(元)");
        cell.setCellStyle(style);
        cell = row.createCell((short) 6);
        cell.setCellValue("合计(元)");
        cell.setCellStyle(style);
        int i = 1;
        if (!wujunMap.isEmpty()) {
            row = sheet.createRow((int) (i));
            row.createCell((short) 0).setCellValue(department.getDepartmentName());
            Region region1 = new Region(i, (short) 0, i - 1 + wujunRow + qingjieRow + chengbenRow + queshiRow + zhouzhuanRow, (short) 0);
            sheet.addMergedRegion(region1);
            row.createCell((short) 1).setCellValue("一次无菌物品");
            Region region2 = new Region(i, (short) 1, i - 1 + wujunRow, (short) 1);
            sheet.addMergedRegion(region2);
            for (Map.Entry<String, PinmingNumVO> entry : wujunMap.entrySet()) {
                row.createCell((short) 2).setCellValue(entry.getValue().getProductName());
                row.createCell((short) 3).setCellValue(entry.getValue().getNum());
                row.createCell((short) 4).setCellValue(entry.getValue().getProductPrice());
                row.createCell((short) 5).setCellValue(entry.getValue().getTotalPrice());
                if (i == 1) {
                    row.createCell((short) 6).setCellValue(wujunHeji);
                    Region region3 = new Region(i, (short) 6, i - 1 + wujunRow, (short) 6);
                    sheet.addMergedRegion(region3);
                }
                row = sheet.createRow((int) (++i));
            }
        }
        if (wujunMap.isEmpty()) {
            row = sheet.createRow((int) (i));
            row.createCell((short) 0).setCellValue(department.getDepartmentName());
            Region region4 = new Region(i, (short) 0, i - 1 + wujunRow + qingjieRow + chengbenRow + queshiRow + zhouzhuanRow, (short) 0);
            sheet.addMergedRegion(region4);
            row.createCell((short) 1).setCellValue("一次无菌物品");
            row.createCell((short) 2).setCellValue("无费用");
            Region region5 = new Region(i, (short) 2, i, (short) 5);
            sheet.addMergedRegion(region5);
            row.createCell((short) 6).setCellValue(wujunHeji);
            i++;
        }
        if (!qingjieMap.isEmpty()) {
            row = sheet.createRow((int) (i));
            row.createCell((short) 1).setCellValue("一次清洁物品");
            Region region6 = new Region(i, (short) 1, i - 1 + qingjieRow, (short) 1);
            sheet.addMergedRegion(region6);
            for (Map.Entry<String, PinmingNumVO> entry : qingjieMap.entrySet()) {
                row.createCell((short) 2).setCellValue(entry.getValue().getProductName());
                row.createCell((short) 3).setCellValue(entry.getValue().getNum());
                row.createCell((short) 4).setCellValue(entry.getValue().getProductPrice());
                row.createCell((short) 5).setCellValue(entry.getValue().getTotalPrice());
                if (i == (wujunRow + 1)) {
                    row.createCell((short) 6).setCellValue(qingjieHeji);
                    Region region7 = new Region(i, (short) 6, i - 1 + qingjieRow, (short) 6);
                    sheet.addMergedRegion(region7);
                }
                row = sheet.createRow((int) (++i));
            }
        }
        if (qingjieMap.isEmpty()) {
            row = sheet.createRow((int) (i));
            row.createCell((short) 1).setCellValue("一次清洁物品");
            row.createCell((short) 2).setCellValue("无费用");
            Region region8 = new Region(i, (short) 2, i, (short) 5);
            sheet.addMergedRegion(region8);
            row.createCell((short) 6).setCellValue(qingjieHeji);
            i++;
        }
        if (!chengbenMap.isEmpty()) {
            row = sheet.createRow((int) (i));
            row.createCell((short) 1).setCellValue("无菌包成本费");
            Region region9 = new Region(i, (short) 1, i - 1 + chengbenRow, (short) 1);
            sheet.addMergedRegion(region9);
            for (Map.Entry<String, PinmingNumVO> entry : chengbenMap.entrySet()) {
                row.createCell((short) 2).setCellValue(entry.getValue().getProductName());
                row.createCell((short) 3).setCellValue(entry.getValue().getNum());
                row.createCell((short) 4).setCellValue(entry.getValue().getProductPrice());
                row.createCell((short) 5).setCellValue(entry.getValue().getTotalPrice());
                if (i == (wujunRow + qingjieRow + 1)) {
                    row.createCell((short) 6).setCellValue(chengbenHeji);
                    Region region10 = new Region(i, (short) 6, i - 1 + chengbenRow, (short) 6);
                    sheet.addMergedRegion(region10);
                }
                row = sheet.createRow((int) (++i));
            }
        }
        if (chengbenMap.isEmpty()) {
            row = sheet.createRow((int) (i));
            row.createCell((short) 1).setCellValue("无菌包成本费");
            row.createCell((short) 2).setCellValue("无费用");
            Region region11 = new Region(i, (short) 2, i, (short) 5);
            sheet.addMergedRegion(region11);
            row.createCell((short) 6).setCellValue(chengbenHeji);
            i++;
        }
        if (!queshiMap.isEmpty()) {
            row = sheet.createRow((int) (i));
            row.createCell((short) 1).setCellValue("缺失费用");
            Region region12 = new Region(i, (short) 1, i - 1 + queshiRow, (short) 1);
            sheet.addMergedRegion(region12);
            for (Map.Entry<String, PinmingNumVO> entry : queshiMap.entrySet()) {
                row.createCell((short) 2).setCellValue(entry.getValue().getProductName());
                row.createCell((short) 3).setCellValue(entry.getValue().getNum());
                row.createCell((short) 4).setCellValue(entry.getValue().getProductPrice());
                row.createCell((short) 5).setCellValue(entry.getValue().getTotalPrice());
                if (i == (wujunRow + qingjieRow + chengbenRow + 1)) {
                    row.createCell((short) 6).setCellValue(queshiHeji);
                    Region region13 = new Region(i, (short) 6, i - 1 + queshiRow, (short) 6);
                    sheet.addMergedRegion(region13);
                }
                row = sheet.createRow((int) (++i));
            }
        }
        if (queshiMap.isEmpty()) {
            row = sheet.createRow((int) (i));
            row.createCell((short) 1).setCellValue("缺失费用");
            row.createCell((short) 2).setCellValue("无费用");
            Region region14 = new Region(i, (short) 2, i, (short) 5);
            sheet.addMergedRegion(region14);
            row.createCell((short) 6).setCellValue(queshiHeji);
            i++;
        }
        if (!zhouzhuanMap.isEmpty()) {
            row = sheet.createRow((int) (i));
            row.createCell((short) 1).setCellValue("周转费");
            Region region15 = new Region(i, (short) 1, i - 1 + zhouzhuanRow, (short) 1);
            sheet.addMergedRegion(region15);
            for (Map.Entry<String, PinmingNumVO> entry : zhouzhuanMap.entrySet()) {
                row.createCell((short) 2).setCellValue(entry.getValue().getProductName());
                row.createCell((short) 3).setCellValue(entry.getValue().getNum());
                if (entry.getValue().getProductPrice() != null) {
                    row.createCell((short) 4).setCellValue(entry.getValue().getProductPrice());
                }
                if (entry.getValue().getTotalPrice() != null) {
                    row.createCell((short) 5).setCellValue(entry.getValue().getTotalPrice());
                }
                if (i == (wujunRow + qingjieRow + chengbenRow + queshiRow + 1)) {
                    row.createCell((short) 6).setCellValue(zhouzhuanHeji);
                    Region region16 = new Region(i, (short) 6, i - 1 + zhouzhuanRow, (short) 6);
                    sheet.addMergedRegion(region16);
                }
                row = sheet.createRow((int) (++i));
            }
        }
        if (zhouzhuanMap.isEmpty()) {
            row = sheet.createRow((int) (i));
            row.createCell((short) 1).setCellValue("周转费");
            row.createCell((short) 2).setCellValue("无费用");
            Region region17 = new Region(i, (short) 2, i, (short) 5);
            sheet.addMergedRegion(region17);
            row.createCell((short) 6).setCellValue(zhouzhuanHeji);
        }
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMM");
            String start = "";
            if (startTime != null) {
                start = simpleDateFormat.format(startTime);
            }
            String fileName = department.getDepartmentName() + start + "计费统计报表.xls";
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.reset();
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);// 指定下载的文件名
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
